// AI助农平台 MongoDB 数据库设计
// 创建时间: 2024年
// 数据库: MongoDB

// 数据库名称
const DB_NAME = 'zhunong_ai_platform';

// 集合设计

// 1. 用户集合 (users)
const usersSchema = {
  collection: 'users',
  indexes: [
    { key: { username: 1 }, unique: true },
    { key: { email: 1 }, unique: true },
    { key: { phone: 1 }, sparse: true },
    { key: { status: 1 } },
    { key: { created_at: -1 } }
  ],
  validator: {
    $jsonSchema: {
      bsonType: 'object',
      required: ['username', 'email', 'password_hash'],
      properties: {
        username: {
          bsonType: 'string',
          minLength: 3,
          maxLength: 50,
          pattern: '^[a-zA-Z0-9_]+$'
        },
        email: {
          bsonType: 'string',
          pattern: '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$'
        },
        phone: {
          bsonType: 'string',
          pattern: '^1[3-9]\\d{9}$'
        },
        password_hash: { bsonType: 'string' },
        real_name: { bsonType: 'string' },
        avatar: { bsonType: 'string' },
        gender: { enum: ['male', 'female', 'other'] },
        birth_date: { bsonType: 'date' },
        address: { bsonType: 'string' },
        is_verified: { bsonType: 'bool' },
        status: { enum: ['active', 'inactive', 'banned'] },
        created_at: { bsonType: 'date' },
        updated_at: { bsonType: 'date' }
      }
    }
  }
};

// 2. 农户集合 (farmers)
const farmersSchema = {
  collection: 'farmers',
  indexes: [
    { key: { user_id: 1 }, unique: true },
    { key: { farm_name: 1 } },
    { key: { status: 1 } },
    { key: { location: '2dsphere' } },
    { key: { created_at: -1 } }
  ],
  validator: {
    $jsonSchema: {
      bsonType: 'object',
      required: ['user_id', 'farm_name', 'farm_address'],
      properties: {
        user_id: { bsonType: 'objectId' },
        farm_name: { bsonType: 'string' },
        farm_address: { bsonType: 'string' },
        farm_size: { bsonType: 'double' },
        farm_type: { enum: ['individual', 'cooperative', 'company'] },
        certification: { bsonType: 'string' },
        description: { bsonType: 'string' },
        contact_person: { bsonType: 'string' },
        contact_phone: { bsonType: 'string' },
        business_license: { bsonType: 'string' },
        location: {
          bsonType: 'object',
          properties: {
            type: { enum: ['Point'] },
            coordinates: {
              bsonType: 'array',
              items: { bsonType: 'double' }
            }
          }
        },
        is_verified: { bsonType: 'bool' },
        verification_date: { bsonType: 'date' },
        status: { enum: ['pending', 'approved', 'rejected', 'suspended'] },
        created_at: { bsonType: 'date' },
        updated_at: { bsonType: 'date' }
      }
    }
  }
};

// 3. 商品分类集合 (categories)
const categoriesSchema = {
  collection: 'categories',
  indexes: [
    { key: { name: 1 }, unique: true },
    { key: { parent_id: 1 } },
    { key: { sort_order: 1 } },
    { key: { is_active: 1 } }
  ],
  validator: {
    $jsonSchema: {
      bsonType: 'object',
      required: ['name'],
      properties: {
        name: { bsonType: 'string' },
        description: { bsonType: 'string' },
        icon: { bsonType: 'string' },
        parent_id: { bsonType: 'objectId' },
        sort_order: { bsonType: 'int' },
        is_active: { bsonType: 'bool' },
        created_at: { bsonType: 'date' },
        updated_at: { bsonType: 'date' }
      }
    }
  }
};

// 4. 商品集合 (products)
const productsSchema = {
  collection: 'products',
  indexes: [
    { key: { farmer_id: 1 } },
    { key: { category_id: 1 } },
    { key: { status: 1 } },
    { key: { price: 1 } },
    { key: { is_featured: 1 } },
    { key: { location: '2dsphere' } },
    { key: { name: 'text', description: 'text' } },
    { key: { created_at: -1 } }
  ],
  validator: {
    $jsonSchema: {
      bsonType: 'object',
      required: ['farmer_id', 'category_id', 'name', 'price', 'unit'],
      properties: {
        farmer_id: { bsonType: 'objectId' },
        category_id: { bsonType: 'objectId' },
        name: { bsonType: 'string' },
        description: { bsonType: 'string' },
        price: { bsonType: 'double' },
        original_price: { bsonType: 'double' },
        stock_quantity: { bsonType: 'int' },
        unit: { bsonType: 'string' },
        min_order_quantity: { bsonType: 'int' },
        images: { bsonType: 'array' },
        tags: { bsonType: 'array' },
        harvest_date: { bsonType: 'date' },
        expiry_date: { bsonType: 'date' },
        origin_place: { bsonType: 'string' },
        location: {
          bsonType: 'object',
          properties: {
            type: { enum: ['Point'] },
            coordinates: {
              bsonType: 'array',
              items: { bsonType: 'double' }
            }
          }
        },
        quality_grade: { enum: ['A', 'B', 'C'] },
        is_organic: { bsonType: 'bool' },
        is_featured: { bsonType: 'bool' },
        status: { enum: ['draft', 'active', 'inactive', 'sold_out'] },
        view_count: { bsonType: 'int' },
        sale_count: { bsonType: 'int' },
        created_at: { bsonType: 'date' },
        updated_at: { bsonType: 'date' }
      }
    }
  }
};

// 5. 订单集合 (orders)
const ordersSchema = {
  collection: 'orders',
  indexes: [
    { key: { order_no: 1 }, unique: true },
    { key: { user_id: 1 } },
    { key: { farmer_id: 1 } },
    { key: { order_status: 1 } },
    { key: { payment_status: 1 } },
    { key: { created_at: -1 } }
  ],
  validator: {
    $jsonSchema: {
      bsonType: 'object',
      required: ['order_no', 'user_id', 'farmer_id', 'total_amount', 'final_amount'],
      properties: {
        order_no: { bsonType: 'string' },
        user_id: { bsonType: 'objectId' },
        farmer_id: { bsonType: 'objectId' },
        total_amount: { bsonType: 'double' },
        discount_amount: { bsonType: 'double' },
        final_amount: { bsonType: 'double' },
        shipping_fee: { bsonType: 'double' },
        shipping_address: { bsonType: 'string' },
        shipping_contact: { bsonType: 'string' },
        shipping_phone: { bsonType: 'string' },
        payment_method: { enum: ['wechat', 'alipay', 'bank'] },
        payment_status: { enum: ['pending', 'paid', 'failed', 'refunded'] },
        order_status: { enum: ['pending', 'confirmed', 'shipped', 'delivered', 'cancelled', 'refunded'] },
        delivery_company: { bsonType: 'string' },
        tracking_number: { bsonType: 'string' },
        estimated_delivery: { bsonType: 'date' },
        actual_delivery_date: { bsonType: 'date' },
        notes: { bsonType: 'string' },
        created_at: { bsonType: 'date' },
        updated_at: { bsonType: 'date' }
      }
    }
  }
};

// 6. 订单详情集合 (order_items)
const orderItemsSchema = {
  collection: 'order_items',
  indexes: [
    { key: { order_id: 1 } },
    { key: { product_id: 1 } },
    { key: { created_at: -1 } }
  ],
  validator: {
    $jsonSchema: {
      bsonType: 'object',
      required: ['order_id', 'product_id', 'product_name', 'unit_price', 'quantity', 'total_price'],
      properties: {
        order_id: { bsonType: 'objectId' },
        product_id: { bsonType: 'objectId' },
        product_name: { bsonType: 'string' },
        product_image: { bsonType: 'string' },
        unit_price: { bsonType: 'double' },
        quantity: { bsonType: 'int' },
        total_price: { bsonType: 'double' },
        created_at: { bsonType: 'date' }
      }
    }
  }
};

// 7. 购物车集合 (cart_items)
const cartItemsSchema = {
  collection: 'cart_items',
  indexes: [
    { key: { user_id: 1 } },
    { key: { product_id: 1 } },
    { key: { user_id: 1, product_id: 1 }, unique: true },
    { key: { created_at: -1 } }
  ],
  validator: {
    $jsonSchema: {
      bsonType: 'object',
      required: ['user_id', 'product_id', 'quantity'],
      properties: {
        user_id: { bsonType: 'objectId' },
        product_id: { bsonType: 'objectId' },
        quantity: { bsonType: 'int' },
        created_at: { bsonType: 'date' },
        updated_at: { bsonType: 'date' }
      }
    }
  }
};

// 8. 评价集合 (reviews)
const reviewsSchema = {
  collection: 'reviews',
  indexes: [
    { key: { order_id: 1 } },
    { key: { user_id: 1 } },
    { key: { product_id: 1 } },
    { key: { farmer_id: 1 } },
    { key: { rating: 1 } },
    { key: { status: 1 } },
    { key: { created_at: -1 } }
  ],
  validator: {
    $jsonSchema: {
      bsonType: 'object',
      required: ['order_id', 'user_id', 'product_id', 'farmer_id', 'rating'],
      properties: {
        order_id: { bsonType: 'objectId' },
        user_id: { bsonType: 'objectId' },
        product_id: { bsonType: 'objectId' },
        farmer_id: { bsonType: 'objectId' },
        rating: { bsonType: 'int', minimum: 1, maximum: 5 },
        content: { bsonType: 'string' },
        images: { bsonType: 'array' },
        is_anonymous: { bsonType: 'bool' },
        status: { enum: ['pending', 'approved', 'rejected'] },
        created_at: { bsonType: 'date' },
        updated_at: { bsonType: 'date' }
      }
    }
  }
};

// 9. AI种植建议集合 (ai_planting_advice)
const aiPlantingAdviceSchema = {
  collection: 'ai_planting_advice',
  indexes: [
    { key: { farmer_id: 1 } },
    { key: { crop_type: 1 } },
    { key: { advice_type: 1 } },
    { key: { is_applied: 1 } },
    { key: { created_at: -1 } }
  ],
  validator: {
    $jsonSchema: {
      bsonType: 'object',
      required: ['farmer_id', 'crop_type', 'title', 'content'],
      properties: {
        farmer_id: { bsonType: 'objectId' },
        crop_type: { bsonType: 'string' },
        advice_type: { enum: ['planting', 'fertilizer', 'pest_control', 'harvest'] },
        title: { bsonType: 'string' },
        content: { bsonType: 'string' },
        weather_data: { bsonType: 'object' },
        soil_data: { bsonType: 'object' },
        confidence_score: { bsonType: 'double' },
        is_applied: { bsonType: 'bool' },
        applied_date: { bsonType: 'date' },
        created_at: { bsonType: 'date' }
      }
    }
  }
};

// 10. 价格预测集合 (price_predictions)
const pricePredictionsSchema = {
  collection: 'price_predictions',
  indexes: [
    { key: { product_id: 1 } },
    { key: { prediction_date: 1 } },
    { key: { created_at: -1 } }
  ],
  validator: {
    $jsonSchema: {
      bsonType: 'object',
      required: ['product_id', 'predicted_price', 'current_price', 'prediction_date'],
      properties: {
        product_id: { bsonType: 'objectId' },
        predicted_price: { bsonType: 'double' },
        current_price: { bsonType: 'double' },
        prediction_date: { bsonType: 'date' },
        confidence_level: { bsonType: 'double' },
        factors: { bsonType: 'object' },
        created_at: { bsonType: 'date' }
      }
    }
  }
};

// 11. 知识库集合 (knowledge_base)
const knowledgeBaseSchema = {
  collection: 'knowledge_base',
  indexes: [
    { key: { category: 1 } },
    { key: { status: 1 } },
    { key: { is_featured: 1 } },
    { key: { title: 'text', content: 'text' } },
    { key: { created_at: -1 } }
  ],
  validator: {
    $jsonSchema: {
      bsonType: 'object',
      required: ['title', 'content'],
      properties: {
        title: { bsonType: 'string' },
        content: { bsonType: 'string' },
        category: { bsonType: 'string' },
        tags: { bsonType: 'array' },
        author: { bsonType: 'string' },
        view_count: { bsonType: 'int' },
        is_featured: { bsonType: 'bool' },
        status: { enum: ['draft', 'published', 'archived'] },
        created_at: { bsonType: 'date' },
        updated_at: { bsonType: 'date' }
      }
    }
  }
};

// 12. 系统配置集合 (system_configs)
const systemConfigsSchema = {
  collection: 'system_configs',
  indexes: [
    { key: { config_key: 1 }, unique: true }
  ],
  validator: {
    $jsonSchema: {
      bsonType: 'object',
      required: ['config_key'],
      properties: {
        config_key: { bsonType: 'string' },
        config_value: { bsonType: 'string' },
        description: { bsonType: 'string' },
        created_at: { bsonType: 'date' },
        updated_at: { bsonType: 'date' }
      }
    }
  }
};

// 导出所有集合配置
module.exports = {
  DB_NAME,
  collections: {
    users: usersSchema,
    farmers: farmersSchema,
    categories: categoriesSchema,
    products: productsSchema,
    orders: ordersSchema,
    order_items: orderItemsSchema,
    cart_items: cartItemsSchema,
    reviews: reviewsSchema,
    ai_planting_advice: aiPlantingAdviceSchema,
    price_predictions: pricePredictionsSchema,
    knowledge_base: knowledgeBaseSchema,
    system_configs: systemConfigsSchema
  }
}; 